import xlrd, xlwt
import os
import time
import difflib
import json
import pandas as pd
from openpyxl import Workbook


class ReadExcel(object):
    def __init__(self, data_path, sheetname,):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        self.resultNum = 0
        self.sysErrorNum = 0
        self.noAnsValNum = 0
        self.noContentNum = 0

        self.nochatDescNum = 0
        self.wsErrChartNum = 0
        self.wsChartNorNum = 0

        self.nochatTypeNum = 0

    # 数据分析内容统计
    def contentStatis(self, resultVal, sysErrorVal, i):
        # 数据分析：意图识别正常，发送数据分析请求
        if resultVal == 'PASS':
            self.resultNum += 1
        # 数据分析：系统返回错误
        if resultVal == 'PASS' and sysErrorVal.startswith('未获取到返回的statis'):
            # {'error_msg': "('Connection aborted.', RemoteDisconnected('Remote end closed connection without response',))"}
            # {'responseTime': '2023-09-08 18:06:34', 'code': '1', 'status': 500, 'msg': '系统错误'}
            # 本身缺少statistic字段
            self.sysErrorNum += 1

        # 数据分析：内容无法回答， 返回中有unknown  未知
        if resultVal == 'PASS' and sysErrorVal.find('unknown') != -1 or sysErrorVal.find('未知') != -1:
            self.noAnsValNum += 1

        # 数据分析：无内容 ，只有content字段
        if resultVal == 'PASS' and sysErrorVal.find('table') == -1 and sysErrorVal.find('chartDesc') == -1 \
                and sysErrorVal.find('bar') == -1 and sysErrorVal.find('line') == -1 and sysErrorVal.find('柱状图') == -1 \
                and sysErrorVal.find('饼状图') == -1 and sysErrorVal.find('未知') == -1 and sysErrorVal.find('unknown') \
                and sysErrorVal.find('未获取到返回的statistic内容') == -1:
            self.noContentNum += 1
            print(type(self.table.row_values(i)),self.table.row_values(i))
            ws.append(self.table.row_values(i))

    # 数据分析tts描述
    def ttsDesc(self, resultVal, sysErrorVal, i):
        if resultVal == 'PASS' and sysErrorVal.find('未获取到返回的statistic内容') == -1:
            if sysErrorVal.find('chartDesc') == -1:
                wsNoChartDesc.append(self.table.row_values(i))
                self.nochatDescNum += 1
            else:
                if sysErrorVal.find('暂无您要查询的数据信息') != -1 or sysErrorVal.find('没有给出') != -1 \
                        or sysErrorVal.find('没有符合查询的数据') != -1 or sysErrorVal.find('数据无法提供') != -1 \
                        or sysErrorVal.find('无法得知') != -1 or sysErrorVal.find('相关数据缺失') != -1 or sysErrorVal.find(
                    '很抱歉') != -1 \
                        or sysErrorVal.find('无法提供') != -1 or sysErrorVal.find('情况未知') != -1 or sysErrorVal.find(
                    '我不知道') != -1 \
                        or sysErrorVal.find('对不起，没有找到') != -1 or sysErrorVal.find('数据不详') != -1 or sysErrorVal.find(
                    '数量未知') != -1 \
                        or sysErrorVal.find('无法回答') != -1 or sysErrorVal.find('数据准备中...') != -1 \
                        or sysErrorVal.find('8月21日') != -1 \
                        or sysErrorVal.find('2023-07') != -1 or sysErrorVal.find('分别是多少？') != -1:
                    wsErrChartDesc.append(self.table.row_values(i))
                    self.wsErrChartNum += 1
                else:
                    # tts描述正常
                    ws.append(self.table.row_values(i))
                    self.wsChartNorNum += 1

    # 数据分析：按照图表类型分类
    def chatType_step2(self,resultVal, sysErrorVal, cmdVal, i):
        '''
        正常范围：表格/折线图/柱状图/饼状图/柱状图+折线图
        '''
        if resultVal == 'PASS' and sysErrorVal.find('未获取到返回的statistic内容') == -1:
            if sysErrorVal.find('chartType') == -1:
                wsNoChartType.append(self.table.row_values(i))
                self.nochatTypeNum += 1
            else:
                # 单事件
                if "事件的数量" in cmdVal and "非机动车闯红灯和机动车闯红灯" not in cmdVal:
                    if sysErrorVal.find('"chartType": "折线图"') != -1 \
                            or sysErrorVal.find('"chartType": "柱状图"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsLineOrBar.append(self.table.row_values(i))
                if "变化趋势" in cmdVal and "非机动车闯红灯和机动车闯红灯" not in cmdVal:
                    if sysErrorVal.find('"chartType": "折线图"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsLine.append(self.table.row_values(i))
                if "在所有事件中的占比" in cmdVal and "非机动车闯红灯和机动车闯红灯" not in cmdVal:
                    if sysErrorVal.find('"chartType": "饼状图"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsPie.append(self.table.row_values(i))
                if "事件数量和占比" in cmdVal and "非机动车闯红灯和机动车闯红灯" not in cmdVal:
                    if sysErrorVal.find('"chartType": "柱状图+折线图"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsBarAndLine.append(self.table.row_values(i))
                if "事件的排名" in cmdVal and "非机动车闯红灯和机动车闯红灯" not in cmdVal:
                    if sysErrorVal.find('"chartType": "表格"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsChart.append(self.table.row_values(i))
                # 双事件
                if "事件的数量" in cmdVal and "非机动车闯红灯和机动车闯红灯" in cmdVal:
                    if sysErrorVal.find('"chartType": "折线图"') != -1 \
                        or sysErrorVal.find('"chartType": "柱状图"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsLineOrBar.append(self.table.row_values(i))
                if "变化趋势" in cmdVal and "非机动车闯红灯和机动车闯红灯" in cmdVal:
                    if sysErrorVal.find('"chartType": "折线图"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsLine.append(self.table.row_values(i))
                if "在所有事件中的占比" in cmdVal and "非机动车闯红灯和机动车闯红灯" in cmdVal:
                    if sysErrorVal.find('"chartType": "表格"') != -1 or sysErrorVal.find('"chartType": "饼状图"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsChartOrPie.append(self.table.row_values(i))
                if "事件数量和占比" in cmdVal and "非机动车闯红灯和机动车闯红灯" in cmdVal:
                    if sysErrorVal.find('"chartType": "表格"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsChart.append(self.table.row_values(i))
                if "事件的排名" in cmdVal and "非机动车闯红灯和机动车闯红灯" in cmdVal:
                    if sysErrorVal.find('"chartType": "表格"') != -1:
                        ws.append(self.table.row_values(i))
                    else:
                        wsChart.append(self.table.row_values(i))

    # 数据分析：按数据问题分类，单独拿出双事件数据
    def chatType_step1(self,resultVal, sysErrorVal, cmdVal, i):

        def filterData():
            # 筛选 数据输出的内容
            if sysErrorVal.find('"table"') == -1 and sysErrorVal.find('"pieOption"') == -1 \
                    and sysErrorVal.find('"lineBarOption"') == -1:
                ws1NoReturn.append(self.table.row_values(i))
            else:
                if sysErrorVal.find('"series": [{"data": []') != -1:
                    ws1NoData.append(self.table.row_values(i))
                elif sysErrorVal.find('"xAxis": {"data": []') != -1:
                    ws1MisX.append(self.table.row_values(i))
                elif "非机动车闯红灯和机动车闯红" in cmdVal:
                    ws1DoubleEvent.append(self.table.row_values(i))
                else:
                    ws1.append(self.table.row_values(i))

        if resultVal == 'PASS' and sysErrorVal.find('未获取到返回的statistic内容') == -1:
            filterData()
        else:
            ws1NoStatistic.append(self.table.row_values(i))


    # 数据分析保存数据
    def saveData(self, saveFileName):

        if saveFileName == '数据分析内容':
            resultValList.append(self.resultNum)
            sysErrorList.append(self.sysErrorNum)
            noAnswerList.append(self.noAnsValNum)
            noContentList.append(self.noContentNum)
            wb.save(data_path + '../' + genFileName + '.xlsx')
        elif saveFileName == 'tts描述':
            nochatDescList.append(self.nochatDescNum)
            errChartList.append(self.wsErrChartNum)
            chartNorList.append(self.wsChartNorNum)
            wb.save(data_path + '../' + genFileName + '.xlsx')
        elif genFileName == 'chat类型校验':
            nochatTypeList.append(self.nochatTypeNum)
            if chatType == '按问题类型分类':
                wb1.save(data_path + '../' + chatType + '.xlsx')
            elif chatType == '按图表类型分类':
                wb.save(data_path + '../' + chatType + '.xlsx')

    def main(self, genFileName):
        for i in range(1, self.rowNum):
            # 获取单元格数据
            resultVal = self.table.cell_value(i, 3)
            sysErrorVal = self.table.cell_value(i, 19)
            cmdVal = self.table.cell_value(i, 0)
            if genFileName == '数据分析内容':
                self.contentStatis(resultVal, sysErrorVal, i)
            elif genFileName == 'tts描述':
                self.ttsDesc(resultVal, sysErrorVal, i)
            elif genFileName == 'chat类型校验':
                if chatType == '按图表类型分类':
                    self.chatType_step2(resultVal, sysErrorVal, cmdVal, i)
                elif chatType == '按问题类型分类':
                    self.chatType_step1(resultVal, sysErrorVal, cmdVal, i)
                else:
                    print('chatType值不为"按图表类型分类"或"按问题类型分类其中之一"')
        # 保存data
        self.saveData(genFileName)
        time.sleep(1)

def logPrint(saveFileName):
    if saveFileName == '数据分析内容':
        print('数据分析总数', sum(resultValList))
        print('返回报错次数', sum(sysErrorList))
        print('内容无法回答总次数', sum(noAnswerList))
        print('无内容总次数', sum(noContentList))
    elif saveFileName == 'tts描述':
        print('tts中无chartDesc字段总次数', sum(nochatDescList))
        print('tts中chartDesc字段错误总次数', sum(errChartList))
        print('tts中chartDesc字段正确总次数', sum(chartNorList))
    elif genFileName == 'chat类型校验':
        print('返回值中无chatType字段总次数', sum(nochatTypeList))


if __name__ == '__main__':
    data_path = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/数据分析/'
    sheetname = 'Sheet1'
    # sheetname = '数据输出正常'
    # sheetname = 'tts描述正常'

    # genFileName = '数据分析内容'
    # genFileName = 'tts描述'
    genFileName = 'chat类型校验'

    # chatType = '按图表类型分类'
    chatType = '按问题类型分类'

    # 统计数据分析内容
    resultValList = []  # pass数量
    sysErrorList = []
    noAnswerList = []
    noContentList = []

    nochatDescList = []
    errChartList = []
    chartNorList = []

    nochatTypeList = []


    wb = Workbook()
    ws = wb.active
    if genFileName == 'chat类型校验':
        if chatType == '按图表类型分类':
            ws.title = "chartType匹配正常"
            wsNoChartType = wb.create_sheet('缺少chartType字段')
            wsLineOrBar = wb.create_sheet('结果不为折线图或柱状图')
            wsLine = wb.create_sheet('结果不为折线图')
            wsPie = wb.create_sheet('结果不为饼状图')
            wsBarAndLine = wb.create_sheet('结果不为柱状图+折线图')
            wsChart = wb.create_sheet('结果不为表格')
            wsChartOrPie = wb.create_sheet('结果不为表格或饼状图')
        elif chatType == '按问题类型分类':
            wb1 = Workbook()
            ws1= wb1.active
            ws1.title = "大部分正常数据"
            ws1NoStatistic = wb1.create_sheet("未获取到返回的statistic内容")
            ws1NoReturn = wb1.create_sheet("未生成图表数据明细")
            ws1NoData = wb1.create_sheet("data列表为空")
            ws1MisX = wb1.create_sheet("缺少横坐标数据")
            ws1DoubleEvent = wb1.create_sheet("双事件筛选")

    elif genFileName == 'tts描述':
        ws.title = "tts描述正常"
        wsNoChartDesc = wb.create_sheet('缺少chartDesc字段')
        wsErrChartDesc = wb.create_sheet('tts描述错误')
    else:
        pass
    # 获取excel数据
    files_list = os.listdir(data_path)
    for file_name in files_list:
        if not file_name.startswith('.~') and file_name.endswith('.xlsx'):
            read_obj = ReadExcel(data_path+file_name, sheetname)
            read_obj.main(genFileName)

    logPrint(genFileName)










